using System;
using System.Collections.Generic;
using System.Text;

using System.CodeDom;
using System.CodeDom.Compiler;
using System.Runtime.InteropServices;
using System.Data;
using Microsoft.CSharp;
using System.IO;

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
using System.Xml;
using System.Diagnostics;
using System.Threading;

namespace DataSetGeneration
{
    public class DataSetGenerator
    {
        private ServerConnection _sc;
        private Database _database;
        private string _xsdExePath;

        public DataSetGenerator(string server, string username, string password, string database, string xsdExeFullPath)
        {
            _sc = new ServerConnection(server, username, password);
            Server s = new Server(_sc);

            try
            {
                _database = s.Databases[database];
            }
            catch (Exception ex)
            {
                throw new ArgumentException("Connecton could not be established or database does not exist. " +
                    "Check the connection parameters and the server is available at this time: " + ex.Message);
            }

            _xsdExePath = xsdExeFullPath;
        }

        ~DataSetGenerator()
        {
          if (_sc != null && _sc.IsOpen)
            _sc.Disconnect();
        }

        #region GenerateDataset

        public void GenerateDataset(string outputDirectory, string datasetName, string datasetNamespace, string primaryTableName, List<string> childTableNames)
        {
            string result = string.Empty;
            DataSet ds = new DataSet();
            ds.DataSetName = datasetName;

            Table primaryTable = null;
            List<Table> childTables = new List<Table>();

            try
            {
                primaryTable = _database.Tables[primaryTableName];

                foreach (string tableName in childTableNames)
                {
                    childTables.Add(_database.Tables[tableName]);
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException("Table could not be found in database. " +
                    "Check the definition file and the database connected to: " + ex.Message);
            }

            using (StreamWriter sw = new StreamWriter(string.Format(@"{0}\\{1}.xsd", outputDirectory, datasetName), false))
            {
                sw.Write(GenerateDataSetXSD(datasetName, primaryTable, childTables));
            }

            try
            {
                // Generates the dataset class file under name.cs
                ProcessStartInfo startInfoXsdExe = new ProcessStartInfo(_xsdExePath, string.Format("\"{0}\\{1}.xsd\" /dataset /l:cs /n:{2} /o:\"{0}\"", outputDirectory, datasetName, datasetNamespace));
                startInfoXsdExe.WindowStyle = ProcessWindowStyle.Hidden;
                
                Process.Start(startInfoXsdExe);
            }
            catch (Exception ex)
            {
                throw new ArgumentException("Could not find xsd.exe in the given path or the command " + 
                    "line parameters are not valid. Generation has failed: " + ex.Message);
            }

            // Alternative method for generation - the ms generator library generator differs
            // to xsd.exe and will produce incorrect files

            /*MemoryStream ms = new MemoryStream();
            DataTable dt = new DataTable();
            ms.Write(System.Text.Encoding.ASCII.GetBytes(generatedSchema), 0, generatedSchema.Length);
            ms.Position = 0;
            ds.ReadXmlSchema(ms);

            // Initialise Code Generator components 
            ICodeGenerator icg = new CSharpCodeProvider().CreateGenerator();
            CodeCompileUnit ccu = new CodeCompileUnit();
            CodeNamespace cn = new CodeNamespace(datasetNamespace);

            // Setup Code Compile Unit 
            ccu.Namespaces.Add(cn);
            ccu.ReferencedAssemblies.Add("System.dll");
            ccu.ReferencedAssemblies.Add("System.Data.dll");


            // Perform Code Generation 
            StringWriter sw = new StringWriter();
            TypedDataSetGenerator.Generate(ds, cn, icg);

            CodeGeneratorOptions cgo = new CodeGeneratorOptions();

            icg.GenerateCodeFromCompileUnit(ccu, sw, null);
            sw.Flush();*/

            // Wait for the file to come available before trying to read it
            Thread.Sleep(100);

            // Rename the file to name.Designer.cs
            string outputFile = string.Format("{0}\\{1}.Designer.cs", outputDirectory, datasetName);

            if (File.Exists(outputFile))
            {
                File.Delete(outputFile);

                // Wait for it to register as deleted
                Thread.Sleep(100);
            }

            File.Move(string.Format("{0}\\{1}.cs", outputDirectory, datasetName), outputFile);

            // Generate the empty cs file
            using (StreamWriter sw = new StreamWriter(string.Format(@"{0}\\{1}.cs", outputDirectory, datasetName), false))
            {
                sw.WriteLine(string.Format("namespace {0} {{", datasetNamespace));
                sw.WriteLine(string.Format("  partial class {0}", datasetName));
                sw.WriteLine("  {");
                sw.WriteLine("  }");
                sw.WriteLine("}");
            }

            // Generate the xsc and xss files - these files are not complete and layout
            // will be lost on regeneration - they are the default stub just written for consistancy
            using (StreamWriter sw = new StreamWriter(string.Format(@"{0}\\{1}.xsc", outputDirectory, datasetName), false))
            {
                sw.WriteLine(@"<?xml version=""1.0"" encoding=""utf-8""?>");
                sw.WriteLine(@"<!--<autogenerated>");
                sw.WriteLine(@"This code was generated by a tool.");
                sw.WriteLine(@"Changes to this file may cause incorrect behavior and will be lost if");
                sw.WriteLine(@"the code is regenerated.");
                sw.WriteLine(@"</autogenerated>-->");
                sw.WriteLine(@"<DataSetUISetting Version=""1.00"" xmlns=""urn:schemas-microsoft-com:xml-msdatasource"">");
                sw.WriteLine(@"<TableUISettings>");
                sw.WriteLine(@"</TableUISettings>");
                sw.WriteLine(@"</DataSetUISetting>");
            }

            using (StreamWriter sw = new StreamWriter(string.Format(@"{0}\\{1}.xss", outputDirectory, datasetName), false))
            {
                sw.WriteLine(@"<?xml version=""1.0"" encoding=""utf-8""?>");
                sw.WriteLine(@"<!--<autogenerated>");
                sw.WriteLine(@"This code was generated by a tool to store the dataset designer's layout information.");
                sw.WriteLine(@"Changes to this file may cause incorrect behavior and will be lost if");
                sw.WriteLine(@"the code is regenerated.");
                sw.WriteLine(@"</autogenerated>-->");
                sw.WriteLine(@"<DiagramLayout xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" ex:showrelationlabel=""False"" ViewPortX=""436"" ViewPortY=""-47"" xmlns:ex=""urn:schemas-microsoft-com:xml-msdatasource-layout-extended"" xmlns=""urn:schemas-microsoft-com:xml-msdatasource-layout"">");
                sw.WriteLine(@"<Shapes />");
                sw.WriteLine(@"<Connectors />");
                sw.WriteLine(@"</DiagramLayout>");
            }


        }

        #endregion

        #region GenerateDataSetXSD

        private string GenerateDataSetXSD(string datasetName, Table primaryTable, List<Table> fringeTables)
        {
            StringBuilder sb = new StringBuilder();

            XmlDocument doc = new XmlDocument();

            List<TableWrapper> tableWrappers = new List<TableWrapper>();

            TableWrapper primaryTableWrapper = new TableWrapper(primaryTable);
            tableWrappers.Add(primaryTableWrapper);

            InsertXmlHeader(ref sb, datasetName);

            sb.AppendLine(string.Format(@"  <xs:element name=""{0}"" msdata:IsDataSet=""true"" msdata:UseCurrentLocale=""true"" msprop:Generator_UserDSName=""{0}"" msprop:Generator_DataSetName=""{0}"">", datasetName));
            sb.AppendLine(@"    <xs:complexType>");
            sb.AppendLine(@"      <xs:choice minOccurs=""0"" maxOccurs=""unbounded"">");

            InsertXmlTableSchema(ref sb, primaryTableWrapper);

            foreach (Table t in fringeTables)
            {
                TableWrapper tableWrapper = new TableWrapper(t);
                InsertXmlTableSchema(ref sb, tableWrapper);
                tableWrappers.Add(tableWrapper);
            }

            sb.AppendLine(@"      </xs:choice>");
            sb.AppendLine(@"    </xs:complexType>");

            foreach (TableWrapper tw in tableWrappers)
            {
                InsertXmlPrimaryKeyElement(ref sb, tw);
            }

            sb.AppendLine(@"  </xs:element>");

            InsertXmlRelationshipAnnotation(ref sb, tableWrappers);

            InsertXmlFooter(ref sb);

            // Load the xml into a doc to make sure its ok
            doc.LoadXml(sb.ToString());

            return doc.InnerXml;
        }

        #endregion

        #region XSD Components

        private void InsertXmlHeader(ref StringBuilder sb, string datasetName)
        {
            sb.AppendLine(@"<?xml version=""1.0"" encoding=""utf-8""?>");

            sb.AppendLine(string.Format(@"<xs:schema id=""{0}"" targetNamespace=""http://tempuri.org/{0}.xsd"" xmlns:mstns=""http://tempuri.org/{0}.xsd"" xmlns=""http://tempuri.org/{0}.xsd"" xmlns:xs=""http://www.w3.org/2001/XMLSchema"" xmlns:msdata=""urn:schemas-microsoft-com:xml-msdata"" xmlns:msprop=""urn:schemas-microsoft-com:xml-msprop"" attributeFormDefault=""qualified"" elementFormDefault=""qualified"">", datasetName));   
        }

        private void InsertXmlTableSchema(ref StringBuilder sb, TableWrapper tableWrapper)
        {
            sb.AppendLine(string.Format(@"        <xs:element name=""{0}"" msprop:Generator_UserTableName=""{0}"" msprop:Generator_RowDeletedName=""{0}RowDeleted"" msprop:Generator_TableClassName=""{0}DataTable"" msprop:Generator_RowChangedName=""{0}RowChanged"" msprop:Generator_RowClassName=""{0}Row"" msprop:Generator_RowChangingName=""{0}RowChanging"" msprop:Generator_RowEvArgName=""{0}RowChangeEvent"" msprop:Generator_RowEvHandlerName=""{0}RowChangeEventHandler"" msprop:Generator_TablePropName=""_{0}"" msprop:Generator_TableVarName=""table{0}"" msprop:Generator_RowDeletingName=""{0}RowDeleting"">", tableWrapper.PrettyTableName));
            sb.AppendLine(@"          <xs:complexType>");
            sb.AppendLine(@"            <xs:sequence>");
            
            if (tableWrapper.PrimaryKeyColumn.DataType.ToString().ToLower() == "uniqueidentifier")
            {
              sb.AppendLine(string.Format(@"              <xs:element name=""{0}"" msdata:DataType=""System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" type=""{1}"" />",
                Utils.GetPrettyObjectName(tableWrapper.PrimaryKeyColumn.Name), GetXmlType(tableWrapper.PrimaryKeyColumn.DataType)));
            }
            else
            {
              sb.AppendLine(string.Format(@"              <xs:element name=""{0}"" msdata:ReadOnly=""true"" msdata:AutoIncrement=""true"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" type=""{1}"" />", Utils.GetPrettyObjectName(tableWrapper.PrimaryKeyColumn.Name), GetXmlType(tableWrapper.PrimaryKeyColumn.DataType)));
            }

            foreach (Column c in tableWrapper.NonPrimaryKeyColumns)
            {
                sb.AppendLine(GetNonPrimaryColumnXmlStub(c));
            }

            sb.AppendLine(@"            </xs:sequence>");
            sb.AppendLine(@"          </xs:complexType>");
            sb.AppendLine(@"        </xs:element>");
        }

        private void InsertXmlPrimaryKeyElement(ref StringBuilder sb, TableWrapper tableWrapper)
        {
            sb.AppendLine(string.Format(@"    <xs:unique name=""{0}_Constraint1"" msdata:ConstraintName=""{0}_Constraint1"" msdata:PrimaryKey=""true"">", tableWrapper.PrettyTableName));
            sb.AppendLine(string.Format(@"      <xs:selector xpath="".//mstns:{0}"" />", tableWrapper.PrettyTableName));
            sb.AppendLine(string.Format(@"      <xs:field xpath=""mstns:{0}"" />", Utils.GetPrettyObjectName(tableWrapper.PrimaryKeyColumn.Name)));
            sb.AppendLine(@"    </xs:unique>");
        }

        private void InsertXmlRelationshipAnnotation(ref StringBuilder sb, List<TableWrapper> tableWrappers)
        {
            bool firstInsert = true;
            bool inserted = false;

            foreach(TableWrapper tw in tableWrappers)
            {
                foreach (ForeignKey fk in tw.Table.ForeignKeys)
                {
                    TableWrapper foreignTable = tw;
                    TableWrapper primaryTable = tableWrappers.Find(
                                                delegate(TableWrapper t)
                                                {
                                                    return (t.PrettyTableName == Utils.GetPrettyObjectName(fk.ReferencedTable));
                                                }
                                              );

                    if (primaryTable != null)
                    {
                        if (firstInsert)
                        {
                            sb.AppendLine(@"  <xs:annotation>");
                            sb.AppendLine(@"    <xs:appinfo>");
                            firstInsert = false;
                            inserted = true;
                        }
                        
                        sb.AppendLine(string.Format(@"        <msdata:Relationship name=""{0}"" msdata:parent=""{1}"" msdata:child=""{2}"" msdata:parentkey=""{3}"" msdata:childkey=""{4}"" msprop:Generator_UserRelationName=""{0}"" msprop:Generator_RelationVarName=""relation{0}"" msprop:Generator_UserChildTable=""{2}"" msprop:Generator_UserParentTable=""{1}"" msprop:Generator_ParentPropName=""{1}Row"" msprop:Generator_ChildPropName=""Get{2}Rows"" />",
                            fk.Name, primaryTable.PrettyTableName, foreignTable.PrettyTableName, Utils.GetPrettyObjectName(primaryTable.PrimaryKeyColumn.Name), Utils.GetPrettyObjectName(fk.Columns[0].Name)));
                    }
                }
            }

            if (inserted)
            {
                sb.AppendLine(@"    </xs:appinfo>");
                sb.AppendLine(@"  </xs:annotation>");
            }
        }

        private void InsertXmlFooter(ref StringBuilder sb)
        {
            sb.AppendLine(@"</xs:schema>");
        }

        private string GetNonPrimaryColumnXmlStub(Column column)
        {
            string objectName = Utils.GetPrettyObjectName(column.Name);
            string colunmType = GetXmlType(column.DataType);

            string optionalComponent = string.Empty;

            if (column.Nullable)
                optionalComponent = @"minOccurs=""0""";

            if (column.IsForeignKey)
            {
              if (column.DataType.ToString().ToLower() == "uniqueidentifier")
              {
                return string.Format(@"<xs:element name=""{0}"" msdata:DataType=""System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" type=""{1}"" {2} />", objectName, colunmType, optionalComponent);
              }
              else
              {
                return string.Format(@"              <xs:element name=""{0}"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" type=""{1}"" {2}/>", objectName, colunmType, optionalComponent);
              }
             }
            else
            {
                StringBuilder sb = new StringBuilder();

                if (column.DataType.ToString().ToLower() == "uniqueidentifier")
                {
                    sb.AppendLine(string.Format(@"<xs:element name=""{0}"" msdata:DataType=""System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" type=""{1}"" {2} />", objectName, colunmType, optionalComponent));
                }
                else if (column.DataType.ToString().ToLower() == "timestamp")
                {
                    sb.AppendLine(string.Format(@"<xs:element name=""{0}"" msdata:ReadOnly=""true"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" type=""{1}"" {2} />", objectName, colunmType, optionalComponent));
                }
                else
                {
                    switch (colunmType)
                    {
                        case ("xs:string"):

                            sb.AppendLine(string.Format(@"              <xs:element name=""{0}"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" {1}>", objectName, optionalComponent));
                            sb.AppendLine(@"                <xs:simpleType>");
                            sb.AppendLine(string.Format(@"                  <xs:restriction base=""{0}"">", colunmType));
                            if (column.DataType.MaximumLength == -1)
                            {
                              sb.AppendLine(string.Format(@"                    <xs:maxLength value=""{0}"" />", 2147483647));
                            }
                            else
                            {
                              sb.AppendLine(string.Format(@"                    <xs:maxLength value=""{0}"" />", column.DataType.MaximumLength));
                            }
                            sb.AppendLine(@"                  </xs:restriction>");
                            sb.AppendLine(@"                </xs:simpleType>");
                            sb.AppendLine(@"              </xs:element>");

                            break;
                        default:

                            sb.AppendLine(string.Format(@"              <xs:element name=""{0}"" msprop:Generator_UserColumnName=""{0}"" msprop:Generator_ColumnPropNameInRow=""{0}"" msprop:Generator_ColumnVarNameInTable=""column{0}"" msprop:Generator_ColumnPropNameInTable=""{0}Column"" type=""{1}"" {2}/>", objectName, colunmType, optionalComponent));
                            
                            break;
                    }
                }

                return sb.ToString();
            }
        }

        #endregion

        #region Helper methods

        private string GetXmlType(DataType dt)
        {
            string result = string.Empty;

            switch (dt.ToString().ToLower())
            {
                case ("int"):
                    result = "xs:int";
                    break;
                case ("varchar"):
                case ("nvarchar"):
                case ("text"):
                case ("ntext"):
                case ("xml"):
                    result = "xs:string";
                    break;
                case ("datetime"):
                    result = "xs:dateTime";
                    break;
                case ("uniqueidentifier"):
                    result = "xs:string";
                    break;
                case ("bit"):
                    result = "xs:boolean";
                    break;
                case ("timestamp"):
                    result = "xs:base64Binary";
                    break;
                default:
                    break;
            }

            if (result.Length == 0)
                throw new ArgumentException("Column type is not recognized");
            else
                return result;
        }

        #endregion
    }
}
